﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace ParkService
{
    public partial class ParkService : ServiceBase
    {
        private IniFiles iniFiles = new IniFiles(AppDomain.CurrentDomain.BaseDirectory + @"\Config.ini");
        private string ConnectString = "";
        private string ServerAddress = "";
        private string Database_Name = "";
        private string Database_User = "";
        private string Database_Pwd = "";
        private System.Timers.Timer timer = new System.Timers.Timer { Interval = 10000 };
        private SqlServerHelper sqlServerHelper = null;

        public ParkService()
        {
            InitializeComponent();
            readConfigFile();
            ConnectString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};pwd={3}",
                ServerAddress, Database_Name, Database_User, Database_Pwd);
            sqlServerHelper = new SqlServerHelper(ConnectString);
            //创建Socket
            timer.Elapsed += new System.Timers.ElapsedEventHandler(MyTask);
            timer.Enabled = true;
        }

        protected override void OnStart(string[] args)
        {
            Logger.WriteLog("服务启动\r\n数据库服务器：" + ServerAddress
                + "\r\n数据库名称：" + Database_Name
                + "\r\n数据库登录名：" + Database_User
                + "\r\n数据库密码：" + Database_Pwd);
            timer.Start();
        }

        protected override void OnStop()
        {
            Logger.WriteLog("服务停止");
            timer.Stop();
        }

        private void MyTask(object sender, System.Timers.ElapsedEventArgs e)
        {
            try
            {
                string LogStr = "";
                Logger.WriteLog("Running... 查询所有控制器ip地址。");
                if (sqlServerHelper.ConnectTest())//检查数据库连接
                {
                    // TODO: 执行定时任务
                    DataTable dataTableEquipment = sqlServerHelper.ReturnDataTable("select * from Equipment where TypeNum=1 order by EditDateTime desc");
                    
                }
                else
                {
                    LogStr += "数据库连接异常，将会持续尝试重新连接。"
                        + "\r\n请检查Service/Config.ini的配置参数是否正确、请检查数据库服务器的配置是否正确。"
                        + "\r\n数据库服务器：" + ServerAddress
                        + "\r\n数据库名称：" + Database_Name
                        + "\r\n数据库登录名：" + Database_User
                        + "\r\n数据库密码：" + Database_Pwd;
                    readConfigFile();
                    string connectString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};pwd={3}",
                        ServerAddress, Database_Name, Database_User, Database_Pwd);
                    setConnectString(connectString);
                }
                if (LogStr.Length != 0) { Logger.WriteLog(LogStr); LogStr = ""; }
            }
            catch (Exception err99)
            {
                Logger.WriteLog(err99.ToString());
            }
        }

        /// <summary>
        /// 读取ini配置文件
        /// </summary>
        private void readConfigFile()
        {
            if (!iniFiles.ExistINIFile())
            {
                iniFiles.WriteString("Sql_Server_Config", "ServerAddress", ".");
                iniFiles.WriteString("Sql_Server_Config", "Database_Name", "AreaGuide");
                iniFiles.WriteString("Sql_Server_Config", "Database_User", "sa");
                iniFiles.WriteString("Sql_Server_Config", "Database_Pwd", "Xuxingxing1");
            }
            if (iniFiles.ExistINIFile())
            {
                try
                {
                    ServerAddress = iniFiles.ReadString("Sql_Server_Config", "ServerAddress");
                    Database_Name = iniFiles.ReadString("Sql_Server_Config", "Database_Name");
                    Database_User = iniFiles.ReadString("Sql_Server_Config", "Database_User");
                    Database_Pwd = iniFiles.ReadString("Sql_Server_Config", "Database_Pwd");
                }
                catch { }
            }
        }

        /// <summary>
        /// 改变了数据库的连接参数，需要重新调整数据库参数
        /// </summary>
        /// <param name="connectString"></param>
        public void setConnectString(string connectString)
        {
            if (connectString.CompareTo(this.ConnectString) != 0)
            {
                this.ConnectString = connectString;
                sqlServerHelper.ChangeConnect(this.ConnectString);
            }
        }

        /// <summary>
        /// 测试数据库的连接
        /// </summary>
        /// <returns></returns>
        public bool ConnectTest()
        {
            return sqlServerHelper.ConnectTest();
        }

        /// <summary>
        /// 检测数据库是否存在
        /// </summary>
        /// <returns></returns>
        public bool DbExist(string dbname, string connectString_master, string connectString_dbname)
        {
            bool flag = false;

            string sql = "select * from sysdatabases where name = '" + dbname + "'";
            try
            {
                sqlServerHelper.ChangeConnect(connectString_master);
                DataTable dataTable = sqlServerHelper.ReturnDataTable(sql);
                if (dataTable.Rows.Count > 0)
                {
                    flag = true;
                }
                sqlServerHelper.ChangeConnect(connectString_dbname);
            }
            catch (Exception e)
            {
                Logger.WriteLog("查找数据库失败\r\n" + e.ToString());
            }
            return flag;
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        public void DbCreate(string dbname, string connectString_master, string connectString_dbname, string path)
        {
            string sql1 = "if exists(select * from sysdatabases where name = '" + dbname + "') "
                + "drop database " + dbname;
            string sql2 = "CREATE DATABASE " + dbname + " ON PRIMARY "
                + "(name=test_data, filename = '" + path + @"\" + dbname + ".mdf', size=3,"
                + "maxsize=5, filegrowth=10%) log on "
                + "(name=mydbb_log, filename='" + path + @"\" + dbname + ".ldf',size=3,"
                + "maxsize=20,filegrowth=1)";
            try
            {
                sqlServerHelper.ChangeConnect(connectString_master);
                sqlServerHelper.ExecuteNonQuery(sql1);
                sqlServerHelper.ExecuteNonQuery(sql2);
                sqlServerHelper.ChangeConnect(connectString_dbname);
                System.Threading.Thread.Sleep(2000);
                //ExecuteSqlScript(Application.StartupPath + @"\SqlScript\AreaGuide.sql");

                runSqlScriptFile(AppDomain.CurrentDomain.BaseDirectory + @"\SqlScript\AreaGuide.sql");
            }
            catch (Exception e)
            {
                Logger.WriteLog("创建数据库失败\r\n" + e.ToString());
            }
        }

        /// <summary>
        /// 执行sql脚本文件
        /// </summary>
        /// <param name="pathStoreProceduresFile"></param>
        /// <returns></returns>
        private bool runSqlScriptFile(string pathStoreProceduresFile)
        {
            try
            {
                string script = File.ReadAllText(pathStoreProceduresFile);

                // split script on GO command
                System.Collections.Generic.IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$",
                                         RegexOptions.Multiline | RegexOptions.IgnoreCase);
                foreach (string commandString in commandStrings)
                {
                    if (commandString.Trim() != "")
                    {
                        sqlServerHelper.ExecuteNonQuery(commandString);
                    }
                }


                return true;
            }
            catch (Exception ex)
            {
                Logger.WriteLog("执行数据库脚本失败\r\n" + ex.ToString());
                return false;
            }
        }
    }
}
